do "E:/ReplicateBuild/02_code/00_environment/00_set_environment.do"


clear

use "$basedata/estimationdata/All_T_J/principal_pref_est_data.dta", clear

gen posting_month = month(PostingDate)

keep if posting_month>=4 & posting_month<=8

preserve
collapse (mean) posting_month, by(jtidx titleI app_year schoolid)

label var posting_month "Vacancy Posting Month"
	   
collapse (min) min_posting_month=posting_month (max) max_posting_month=posting_month, by(titleI app_year schoolid)

label var min_posting_month "Earliest Vacancy Posting Month"

twoway (histogram min_posting_month if titleI==0, discrete color(green)) ///
       (histogram min_posting_month if titleI==1, discrete ///
	   fcolor(none) lcolor(black)), legend(order(1 "Non-Title I" 2 "Title I" ))
graph export "$output/figures/first_vacancy_month.png", as(png) replace	  

label var max_posting_month "Latest Vacancy Posting Month"

twoway (histogram max_posting_month if titleI==0, discrete color(green)) ///
       (histogram max_posting_month if titleI==1, discrete ///
	   fcolor(none) lcolor(black)), legend(order(1 "Non-Title I" 2 "Title I" ))
graph export "$output/figures/last_vacancy_month.png", as(png) replace	   
restore

preserve

collapse (max) hired (mean) posting_month, by(jtidx titleI app_year schoolid)
   
label var posting_month "Vacancy Posting Month"	   
label var hired "Fill Rate"

gen hired_0 = hired if titleI==0
gen hired_1 = hired if titleI==1

label var hired_0 "Non-Title I"
label var hired_1 "Title I"

graph bar hired_0 hired_1, over(posting_month) ytitle("Fill Rate") legend(order(1 "Non-Title I" 2 "Title I" ))
graph export "$output/figures/fill_rate.png", as(png) replace

restore


/*
DESCRIPTIVELY, SHOW OBSERVABLES (SCHOOL LEVEL) ACROSS VACANCY POSTING PERIODS. % disadv, Title I, etc. APPLICATIONS TO SCHOOL IN LAGGED CYCLES? 
*/
* calculate applications in lagged cycle
preserve
collapse (sum) applied, by(jtidx app_year schoolid)
collapse (mean) applied, by(schoolid app_year)
replace app_year = app_year+1
ren applied lagged_mean_apps
tempfile templaggedapps
save `templaggedapps', replace
restore

preserve
sort schoolid app_year
merge n:1 schoolid app_year using `templaggedapps'
drop if _m==2
drop _m

gen total_n = mean_n1_ma + mean_n2_ma

gen coarse_posting_month = posting_month
replace coarse_posting_month = 5 if posting_month==6
replace coarse_posting_month = 7 if posting_month==8
collapse (mean) p_school_m2_ma p_school_m1_ma_race p_school_m1_ma_ach p_school_m1_ma total_n titleI frac_black frac_hisp attendance_rate school_exceeded school_met school_notmet lagged_mean_apps, by(coarse_posting_month)

local i = 0
foreach var in titleI p_school_m2_ma p_school_m1_ma_race p_school_m1_ma_ach total_n attendance_rate school_exceeded school_met lagged_mean_apps {
	local i = `i'+1
	qui summ `var' if coarse_posting_month==4
	local mean1 = r(mean)
	qui summ `var' if coarse_posting_month==5
	local mean2 = r(mean)
	qui summ `var' if coarse_posting_month==7
	local mean3 = r(mean)
	mat m`i' = (`mean1',`mean2',`mean3')
	gen m`i'var = .
	
}


label var m1var "Title I"
label var m2var "Fraction of Students Economically Disadvantaged"
label var m3var "Fraction of Students Non-White"
label var m4var "Fraction of Students Below Median Lagged Math Scores"
label var m5var "Number of Students per Teacher"
label var m6var "Attendance Rate"
label var m7var "School Exceeded Expectations"
label var m8var "School Met Expectations"
label var m9var "Mean Applications in Previous Hiring Cycle"


forv i=1/9 {
	mat rownames m`i' = m`i'var
}


mat m = m1\m2\m3\m4\m5\m6\m7\m8\m9


frmttable using "$tables/characteristics_by_vacancy_month", statmat(m) replace va tex fra ///
	ctitles("","April","May-June","July-August") ///
	sdec(2,2,2\2,2,2\2,2,2\2,2,2\0,0,0\2,2,2\2,2,2\2,2,2\0,0,0) 

restore

/*
WITHIN SCHOOL, CAN SHOW OFFERED TEACHER’S VA BY POSTING TIME.CAN SHOW APPLICANTS’ VA BY POSTING TIME 
For schools with multiple vacancies, show how outcomes vary by early vs. late
*/

preserve
gen vacancy = 1

collapse (max) vacancy (mean) PostingDate, by(ncerdc_schlcode app_year jtidx)
collapse (sum) vacancy (min) minpostingdate=PostingDate (max) maxpostingdate=PostingDate, by(ncerdc_schlcode app_year)

ren vacancy num_vacancies
tempfile tempnumvacancies
save `tempnumvacancies'
restore

sort ncerdc_schlcode app_year
merge n:1 ncerdc_schlcode app_year using `tempnumvacancies'
assert _m==3
drop _m

keep if num_vacancies==2 & minpostingdate<maxpostingdate

gen firstvacancy = PostingDate==minpostingdate
gen lastvacancy = PostingDate==maxpostingdate
assert firstvacancy+lastvacancy==1

gen va = va_ma_preY_dev + mu_jt_preY_mean_s_ma

gen va_per_student = va/(mean_n1_ma+mean_n2_ma) if va!=0


gen hiredVA = va_per_student if (application_outcome_category=="hired")
gen positiveVA = va_per_student if (application_outcome_category=="positive_assessment" | application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined")
gen applicantVA = va_per_student
gen hiredVAnonmiss = (va_per_student!=.) if (application_outcome_category=="hired")
gen positiveVAnonmiss = (va_per_student!=.) if (application_outcome_category=="positive_assessment" | application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined")
gen applicantVAnonmiss = (va_per_student!=.)

cap drop groupID
egen groupID = group(ncerdc_schlcode app_year)
local i = 0
foreach var in applicantVA applicantVAnonmiss positiveVA positiveVAnonmiss hiredVA hiredVAnonmiss {
	local i = `i'+1
    cap drop nobs countvar wgtvar
	gen countvar = `var'!=.
	bys groupID lastvacancy: egen nobs = sum(countvar)
	gen wgtvar = 1/nobs
	areg `var' lastvacancy [aw=wgtvar], ab(groupID)
	matrix m = r(table)

	global p=m[4,1]
	mat m`i' = (_b[_cons],_b[_cons]+_b[lastvacancy],${p},e(N))
	gen m`i'var = .
	
}


label var m1var "Applicants VA per Student"
label var m2var "Applicants non-Missing VA"
label var m3var "Postitive Asssessments VA per Student"
label var m4var "Postitive Asssessments non-Missing VA"
label var m5var "Hired Teachers VA per Student"
label var m6var "Hired Teachers non-Missing VA"


forv i=1/6 {
	mat rownames m`i' = m`i'var
}


mat m = m1\m2\m3\m4\m5\m6


frmttable using "$tables/vacancy_outcomes_wi_school", statmat(m) replace va tex fra ///
	ctitles("","First vacancy","Second vacancy","p-value on difference","Num obs") ///
	sdec(2,2,3,0\2,2,3,0\2,2,3,0\2,2,3,0\2,2,3,0\2,2,3,0) 

